package org.streets.eis.component.codegroup;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.tapestry5.ioc.annotations.Inject;
import org.apache.wicket.ajax.AjaxRequestTarget;
import org.apache.wicket.model.IModel;
import org.apache.wicket.util.string.Strings;
import org.streets.commons.util.JDBCUtils;
import org.streets.database.Limits;
import org.streets.database.SQLConnection;
import org.streets.database.SQLNestedException;

import org.streets.eis.component.combobox.TreeComboBox;
import org.streets.eis.component.model.DataModel;
import org.streets.eis.component.model.ListItem;

public class TreeCodeGroup extends TreeComboBox {

	private static final long serialVersionUID = 1L;

	private String codeGroupId = "";
	private String rootNodeconditionSql = "";

	@Inject
	private SQLConnection conn;
	
	public TreeCodeGroup(String id, String codeGroupId){
		this(id,codeGroupId,true);
	}
	
	public TreeCodeGroup(String id, String codeGroupId,String rootNodeconditionSql){
		this(id,codeGroupId,rootNodeconditionSql,true);
	}
	
	public TreeCodeGroup(String id, String codeGroupId,boolean canInput){
		this(id,codeGroupId,"",canInput);
	}
	
	public TreeCodeGroup(String id, String codeGroupId,String rootNodeconditionSql,boolean canInput){
		super(id,null,canInput);
		this.rootNodeconditionSql = rootNodeconditionSql;
		setCodeGroupId(codeGroupId);
	}	
	
	public TreeCodeGroup(String id, IModel<ListItem> model,String codeGroupId){
		this(id,model,codeGroupId,true);
	}
	
	public TreeCodeGroup(String id, IModel<ListItem> model,String codeGroupId,String rootNodeconditionSql){
		this(id,model,codeGroupId,rootNodeconditionSql,true);
	}
	
	public TreeCodeGroup(String id, IModel<ListItem> model,String codeGroupId,boolean canInput){
		this(id,model,codeGroupId,"",canInput);
	}
	
	public TreeCodeGroup(String id, IModel<ListItem> model,String codeGroupId,String rootNodeconditionSql,boolean canInput){
		super(id,model,null,canInput);
		this.rootNodeconditionSql = rootNodeconditionSql;
		setCodeGroupId(codeGroupId);
	}
	
	public String getCodeGroupId() {
		return codeGroupId;
	}

	public void setCodeGroupId(String codeGroupId) {
		this.codeGroupId = codeGroupId;
		setDataModel(generateCodeGroupDataModel());
	}
	
	public String getRootNodeConditionSql() {
		return rootNodeconditionSql;
	}

	public void setRootNodeConditionSql(String rootNodeconditionSql) {
		this.rootNodeconditionSql = rootNodeconditionSql;
		setDataModel(generateCodeGroupDataModel());
	}	
	
	public DataModel<ListItem> generateCodeGroupDataModel(){
		if (Strings.isEmpty(codeGroupId)){
			return new DataModel<ListItem>();
		} 
		
		String sql =  String.format("select id,name,(case when exists(select id from eis_code_item where parent_code=a.code) then 1 else 0 end) from eis_code_item a where group_id='%s'  and %s order by code",
				codeGroupId,Strings.isEmpty(rootNodeconditionSql)?"parent_code is null" : rootNodeconditionSql); 
		ResultSet rs = conn.query(sql, Limits.none());
		DataModel<ListItem> model = new DataModel<ListItem>();
		try{
			while (rs.next()){
				DataModel<ListItem> tmp = new DataModel<ListItem>(new ListItem(rs.getString(1),rs.getString(2)));
				if (rs.getInt(3)==1) tmp.addChild(TreeComboBox.DUMMY);
				model.addChild(tmp);
			}
		} catch (Exception ex) {
			throw new SQLNestedException(sql, ex);
		} finally {
			JDBCUtils.close(rs);
		}
		return model;
	}
	
	@Override
	protected Iterator<ListItem> getMatchedItems(String key) {
		if (Strings.isEmpty(codeGroupId)){
			return new ArrayList<ListItem>().iterator();
		} 
		
		String sql ;
		String filterSql = getOnlySelectLeaf() ? String.format("and not exists(select id from eis_code_item b where group_id='%s' and parent_code=eis_code_item.code)",codeGroupId) : "";
		if (Strings.isEmpty(rootNodeconditionSql)){
			sql = String.format("select id,name from eis_code_item where group_id='%s'  and (%s or %s) %s order by code",
					codeGroupId,generateLikeSql("name",key),generateLikeSql("alias",key),filterSql);	
		} else {
			sql = String.format("select id,name from eis_code_item where  (%s or %s)  %s start with group_id='%s' and %s connect by group_id='%s' and parent_code=prior code order by code",
					generateLikeSql("name",key),generateLikeSql("alias",key),filterSql,codeGroupId,rootNodeconditionSql,codeGroupId);		
		}
		ResultSet rs = conn.query(sql, Limits.of(0, 10));
		List<ListItem> matcheds = new ArrayList<ListItem>();
		try{
			while (rs.next()){
				matcheds.add(new ListItem(rs.getString(1),rs.getString(2)));
			}
		} catch (Exception ex) {
			throw new SQLNestedException(sql, ex);
		} finally {
			JDBCUtils.close(rs);
		}
		return matcheds.iterator();	
	}

	@Override
	protected List<DataModel<ListItem>> generateChildrenDataModel(String parentId) {
		if (Strings.isEmpty(codeGroupId)){
			return new ArrayList<DataModel<ListItem>>();
		} 
		String sql =  String.format("select id,name,(case when exists(select id from eis_code_item where parent_code=a.code) then 1 else 0 end) from eis_code_item a  where group_id='%s' and parent_code = '%s' order by code",
				codeGroupId,parentId.split("\\.")[1]);
		ResultSet rs = conn.query(sql, Limits.none());
		DataModel<ListItem> model = new DataModel<ListItem>();
		try{
			while (rs.next()){
				DataModel<ListItem> tmp = new DataModel<ListItem>(new ListItem(rs.getString(1),rs.getString(2)));
				if (rs.getInt(3)==1) tmp.addChild(TreeComboBox.DUMMY);
				model.addChild(tmp);
			}
		} catch (Exception ex) {
			throw new SQLNestedException(sql, ex);
		} finally {
			JDBCUtils.close(rs);
		}
		return model.getChildren();
	}
	
	@Override
	protected void onItemChanged(ListItem newValue){
		onItemChanged(this,newValue);
	}

	@Override
	protected void onAjaxItemChanged(ListItem newValue,AjaxRequestTarget target){
		onAjaxItemChanged(this,newValue,target);
	}		
	
	protected void onItemChanged(TreeCodeGroup codeGroup,ListItem newValue){		
	}
	
	protected void onAjaxItemChanged(TreeCodeGroup codeGroup,ListItem newValue,AjaxRequestTarget target){	
	}	

}
